﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using ZG_member.BLL;
using System.Data.OleDb;
using System.Configuration;

namespace ZG_member
{
    public partial class ProduceForm : Form
    {
        MainForm main;
        public ProduceForm(MainForm mm)
        {
            InitializeComponent();
            main = mm;
            this.Opacity = 0;
            skinEngine1.SkinFile = Application.StartupPath + ConfigurationManager.AppSettings["Skin"];
        }
        public string nameuser;
      
        private void ProduceForm_Load(object sender, EventArgs e)
        {
            this.timer1.Start();
            nameuser = main.username;
            TreeView();
            panel1.Visible = false;
        }
        TreeNode node = new TreeNode();
        private void TreeView()
        {
            treeView1.LabelEdit = true;

            node.Text = "所有产品类别";//可编辑状态。   ，这个结点是根节点。 
            treeView1.Nodes.Add(node);
            goodsType type = new goodsType();
            DataTable dt = type.GetList("").Tables[0];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                TreeNode nd = new TreeNode();
                nd.Text = dt.Rows[i]["typeName"].ToString();
                node.Nodes.Add(nd);
            }
            goods good = new goods();
            DataTable dtt = good.GetList("").Tables[0];
            dgvgoods.DataSource = dtt;
            
   
            //leixinxianshi();

            dgvgoods.AllowUserToAddRows = false;
            Loaddgv();
            treeView1.ExpandAll();
            //cuxiaoxianshi();
            

        }
        goodsType gbll = new goodsType();
        //public void leixinxianshi()
        //{

    
        //    for (int i = 0; i < dgvdaochu.Rows.Count; i++)
        //    {
        //        if (dgvdaochu.Rows[i].Cells["type"].Value.ToString().Trim() == "" || dgvdaochu.Rows[i].Cells["type"].Value.ToString().Trim() == null)
        //        {
        //            return;
        //        }
        //        int id = int.Parse(dgvdaochu.Rows[i].Cells["type"].Value.ToString().Trim());
        //        Model.goodsType gt = gbll.GetModel(id);
        //        if (gt == null)
        //        {
        //            continue;
        //        }
        //        dgvdaochu.Rows[i].Cells["类别"].Value = gt.typeName;
        //        if (dgvdaochu.Rows[i].Cells["GlobalDiscount"].Value.ToString() == "false")
        //        {
        //            dgvdaochu.Rows[i].Cells["GlobalDiscount2"].Value = "否";
        //        }
        //        else
        //        {
        //            dgvdaochu.Rows[i].Cells["GlobalDiscount2"].Value = "是";
        //        }

        //    }
        //}


        public void cuxiaoxianshi()
        {

            for (int i = 0; i < dgvgoods.Rows.Count; i++)
            {
                if (bool.Parse(dgvgoods.Rows[i].Cells["是否促销"].Value.ToString()))
                {
                    dgvgoods.Rows[i].Cells["price"].Value = "-";
                }
                else
                {
                    dgvgoods.Rows[i].Cells["促销售价"].Value = "-";
                }
            }
        }

        public void Loaddgv()
        {
            goodsType goodtype = new goodsType();
            Model.goodsType goodtypeModel = new Model.goodsType();

            foreach (DataGridViewRow dgv in dgvgoods.Rows)
            {
                
                goodtypeModel = goodtype.GetModelList("id='" + dgv.Cells["goodsTypeId"].Value + "'")[0];
                dgv.Cells["typeName1"].Value = goodtypeModel.typeName;
               
            }
            //foreach (DataGridViewRow dgv in dgvdaochu.Rows)
            //{

            //    goodtypeModel = goodtype.GetModelList("id='" + dgv.Cells["type1"].Value + "'")[0];
            //    dgv.Cells["type2"].Value = goodtypeModel.typeName;
            //    if (dgv.Cells["quanju1"].Value.ToString() == "false")
            //    {
            //        dgv.Cells["quanju2"].Value = "否";
            //    }
            //    else
            //    {
            //        dgv.Cells["quanju2"].Value = "是";
            //    }
            //}
        }



        private void treeView1_MouseDown(object sender, MouseEventArgs e)
        {
            if (e.Button == System.Windows.Forms.MouseButtons.Right)
            {
                TreeNode node = treeView1.GetNodeAt(e.X, e.Y);//根据鼠标点击的坐标，获取距离该坐标最近的节点
                if (node != null)
                {
                    treeView1.SelectedNode = node;

                    Point p = new Point(this.Left + e.X, this.Top + e.Y);
                    contextMenuStrip1.Show(p);
                }
            }

        }

        private void toolStripMenuItem1_Click(object sender, EventArgs e)
        {
            if (treeView1.SelectedNode.Text == "所有产品类别")
            {
                panel1.Visible = true;
                click = true;
            }
            else
            {
                MessageBox.Show("请选择所有产品类别！");
            }
            treeView1.ExpandAll();
        }
        private string typename;
        private bool click;
        private void 修改类别ToolStripMenuItem_Click(object sender, EventArgs e)
        {

            if (treeView1.SelectedNode.Text != "所有产品类别")
            {
                treeView1.SelectedNode.BeginEdit();
                typename = treeView1.SelectedNode.Text;
                panel1.Visible = true;
            }
        }
        private void 删除类别ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (treeView1.SelectedNode.Text != "所有产品类别")
            {
                goodsType type = new goodsType();
                Model.goodsType typeModel = new Model.goodsType();
                goods g = new goods();
                typeModel = type.GetModelList("typeName='" + treeView1.SelectedNode.Text + "'")[0];
                List<Model.goods> gmod = g.GetModelList(" goodsTypeId =" + typeModel.id);
                DialogResult dr;

                if (gmod.Count > 0)
                {
                    dr = MessageBox.Show("该类别正在被使用,删除将会连同商品一起删除，请问是否继续删除？", "提示", MessageBoxButtons.YesNo);
                    if (dr == DialogResult.Yes)
                    {
                        g.Deletewhere(" goodsTypeId =" + typeModel.id);
                        type.Delete(typeModel.id);
                    }
                    else
                    {
                        return;
                    }
                }
                else
                {
                    type.Delete(typeModel.id);
                }
                treeView1.Nodes.Remove(treeView1.SelectedNode);

            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (click == true)
            {
                TreeNode nd = new TreeNode();
                nd.Text = txtTypeName.Text;
                goodsType type = new goodsType();
                Model.goodsType typeModel = new Model.goodsType();
                typeModel.typeName = txtTypeName.Text;
                int row = type.Add(typeModel);
                if (row > 0)
                {
                    node.Nodes.Add(nd);
                    click = false;
                    panel1.Visible = false;
                }
            }
            else
            {

                goodsType type = new goodsType();
                Model.goodsType typeModel = new Model.goodsType();
                typeModel = type.GetModelList("typeName='" + typename + "'")[0];
                typeModel.typeName = txtTypeName.Text;
                if (type.Update(typeModel))
                {
                    treeView1.SelectedNode.Text = txtTypeName.Text;
                    panel1.Visible = false;
                }
            }



        }
        public int ID;
        private void btnProduceAdd_Click(object sender, EventArgs e)
        {
            ID = 0;
            GoodsAddForm goods = new GoodsAddForm(this);
            goods.ShowDialog();
        }
        public void updateshangping() {
            if (dgvgoods.Rows.Count < 1)
            {
                return;
            }
            ID = int.Parse(dgvgoods.CurrentRow.Cells["zhujian"].Value.ToString());
            GoodsAddForm goods = new GoodsAddForm(this);
            goods g = new goods();
            Model.goods gmd = g.GetModel(ID);
            if (dgvgoods.CurrentRow.Cells["goodsId"].Value != null)
            {
                goods.txtgoodsId.Text = dgvgoods.CurrentRow.Cells["goodsId"].Value.ToString();
            }
            if (dgvgoods.CurrentRow.Cells["goodsName"].Value != null)
            {
                goods.txtgoodsName.Text = dgvgoods.CurrentRow.Cells["goodsName"].Value.ToString();
            }
            if (dgvgoods.CurrentRow.Cells["amount"].Value != null)
            {
                goods.txtamount.Text = dgvgoods.CurrentRow.Cells["amount"].Value.ToString();
            }
            if (dgvgoods.CurrentRow.Cells["Promotionprice"].Value != null)
            {
                goods.txtPromotionprice.Text = dgvgoods.CurrentRow.Cells["Promotionprice"].Value.ToString();
            }

            if (dgvgoods.CurrentRow.Cells["是否促销"].Value != null)
            {

                goods.rbtnyes.Checked = bool.Parse(dgvgoods.CurrentRow.Cells["是否促销"].Value.ToString());


            }

            //if (dgvgoods.CurrentRow.Cells["GlobalDiscount2"].Value != null)
            //{

            //    goods.radioButton1.Checked = bool.Parse(dgvgoods.CurrentRow.Cells["GlobalDiscount"].Value.ToString());


            //}
            if (dgvgoods.CurrentRow.Cells["price"].Value != null)
            {
                if (dgvgoods.CurrentRow.Cells["price"].Value.ToString() == "-")
                {
                    goods.txtprice.Text = gmd.Promotionprice;
                }
                else
                {
                    goods.txtprice.Text = decimal.Parse(dgvgoods.CurrentRow.Cells["price"].Value.ToString()).ToString("F0");
                }

            }

            if (dgvgoods.CurrentRow.Cells["unit"].Value != null)
            {
                goods.cbxunit.Text = dgvgoods.CurrentRow.Cells["unit"].Value.ToString();
            }
            if (dgvgoods.CurrentRow.Cells["typeName1"].Value != null)
            {
                goods.cbxgoodsTypeId.Text = dgvgoods.CurrentRow.Cells["typeName1"].Value.ToString();
            }
            if (dgvgoods.CurrentRow.Cells["finalCost"].Value != null)
            {
                goods.txtfinalCost.Text = dgvgoods.CurrentRow.Cells["finalCost"].Value.ToString();
            }
            foreach (RadioButton item in goods.panel2.Controls)
            {
                if (item.Text == dgvgoods.CurrentRow.Cells["tichengType"].Value.ToString())
                {
                    item.Checked = true;
                }
            }
            goods.ShowDialog();
            //cuxiaoxianshi();
        }
        private void btnProductUpt_Click(object sender, EventArgs e)
        {
            updateshangping();
        }
        /// <summary>
        /// 选中节点事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
        {
            if (treeView1.SelectedNode.Text != "所有产品类别")
            {
                goodsType goodtype = new goodsType();
                Model.goodsType goodtypeModel = new Model.goodsType();
                goodtypeModel = goodtype.GetModelList("typeName='" + treeView1.SelectedNode.Text + "'")[0];


                goods good = new goods();
                DataTable dt = good.GetList("goodsTypeId='" + goodtypeModel.id + "'").Tables[0];
                dgvgoods.DataSource = dt;
                
                dgvgoods.AllowUserToAddRows = false;
                Loaddgv();
                //DataTable dtdaochu = good.GetList("goodsTypeId='" + goodtypeModel.id + "'").Tables[0];
                //dgvdaochu.DataSource = dtdaochu;
                //leixinxianshi();
            }
            if (treeView1.SelectedNode.Text == "所有产品类别")
            {
                goods good = new goods();
                DataTable dtt = good.GetList("").Tables[0];
                dgvgoods.DataSource = dtt;
                
                dgvgoods.AllowUserToAddRows = false;
                Loaddgv(); 
                //DataTable dtdaochu = good.GetList("").Tables[0];
                //dgvdaochu.DataSource = dtdaochu;
                //leixinxianshi();
            }
        }

        private void btnproductDel_Click(object sender, EventArgs e)
        {
            if (dgvgoods.Rows.Count < 1)
            {
                return;
            }
            int id = int.Parse(dgvgoods.CurrentRow.Cells["zhujian"].Value.ToString());
            goods good = new goods();
            DialogResult dr = MessageBox.Show("确定删除？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
            if (dr == DialogResult.OK)
            {

                if (good.Delete(id))
                {
                    
                    
                    dgvgoods.AllowUserToAddRows = false;
                   
                    //DataTable dtdaochu = good.GetList("").Tables[0];
                    //dgvdaochu.DataSource = dtdaochu;
                    //leixinxianshi();




                    //商品流水信息
                    goodsflowing goods = new goodsflowing();
                    Model.goodsflowing goodsModel = new Model.goodsflowing();

                    goodsModel.username = main.username;//用户名
                    goodsModel.Time = DateTime.Now;

                    goodsModel.operate = "删除商品";


                    //商品流水信息
                    goodsModel.goodsId = dgvgoods.CurrentRow.Cells["goodsId"].Value.ToString();//商品编号
                    goodsModel.goodsName = dgvgoods.CurrentRow.Cells["goodsName"].Value.ToString(); //商品名称
                    goodsModel.unit = dgvgoods.CurrentRow.Cells["unit"].Value.ToString();
                    goodsModel.price = decimal.Parse(dgvgoods.CurrentRow.Cells["price"].Value.ToString());//预售单价
                    goodsModel.Promotionprice = dgvgoods.CurrentRow.Cells["Promotionprice"].Value.ToString();
                    goodsModel.goodsTypeId = dgvgoods.CurrentRow.Cells["typeName1"].Value.ToString();
                    int row = goods.Add(goodsModel);
                    DataTable dtt = good.GetList("").Tables[0];
                    dgvgoods.DataSource = dtt;
                    Loaddgv();
                    if (row <= 0)
                    {
                        MessageBox.Show("系统出现故障！请检查."); return;
                    }

                }
            }
        }
        public bool down;
        private void btnBuyGoods_Click(object sender, EventArgs e)
        {
            if (dgvgoods.Rows.Count < 1)
            {
                return;
            }
            down = false;
            BuyGoods buygood = new BuyGoods(this);
            if (dgvgoods.CurrentRow.Cells["goodsId"].Value != null)
            {
                buygood.labgoodsId.Text = dgvgoods.CurrentRow.Cells["goodsId"].Value.ToString();
            }
            if (dgvgoods.CurrentRow.Cells["goodsName"].Value != null)
            {
                buygood.labgoodsName.Text = dgvgoods.CurrentRow.Cells["goodsName"].Value.ToString();
            }
            if (dgvgoods.CurrentRow.Cells["amount"].Value != null)
            {
                buygood.labamount.Text = dgvgoods.CurrentRow.Cells["amount"].Value.ToString();
            }
            if (dgvgoods.CurrentRow.Cells["price"].Value != null)
            {
               buygood.labprice.Text  = dgvgoods.CurrentRow.Cells["price"].Value.ToString();
            }

            if (dgvgoods.CurrentRow.Cells["finalCost"].Value != null)
            {
                buygood.labfinalCost.Text = dgvgoods.CurrentRow.Cells["finalCost"].Value.ToString();
            }
            //if (dgvgoods.CurrentRow.Cells["unit"].Value != null)
            //{
            //    buygood.unit = dgvgoods.CurrentRow.Cells["unit"].Value.ToString();
            //}
            //if (dgvgoods.CurrentRow.Cells["typeName1"].Value != null)
            //{
            //    buygood.goodsTypeId = dgvgoods.CurrentRow.Cells["typeName1"].Value.ToString();
            //}

            buygood.ShowDialog();
        }

        private void btnDelgoods_Click(object sender, EventArgs e)
        {
            if (dgvgoods.Rows.Count < 1)
            {
                return;
            }
            down = true;
            BuyGoods buygood = new BuyGoods(this);
            if (dgvgoods.CurrentRow.Cells["goodsId"].Value != null)
            {
                buygood.labgoodsId.Text = dgvgoods.CurrentRow.Cells["goodsId"].Value.ToString();
            }
            if (dgvgoods.CurrentRow.Cells["goodsName"].Value != null)
            {
                buygood.labgoodsName.Text = dgvgoods.CurrentRow.Cells["goodsName"].Value.ToString();
            }
            if (dgvgoods.CurrentRow.Cells["amount"].Value != null)
            {
                buygood.labamount.Text = dgvgoods.CurrentRow.Cells["amount"].Value.ToString();
            }
            //if (dgvgoods.CurrentRow.Cells["finalCost"].Value != null)
            //{
            //    buygood.labfinalCost.Text = dgvgoods.CurrentRow.Cells["finalCost"].Value.ToString();
            //}
            if (dgvgoods.CurrentRow.Cells["price"].Value != null)
            {
                buygood.labprice.Text = dgvgoods.CurrentRow.Cells["price"].Value.ToString();
            }
            //if (dgvgoods.CurrentRow.Cells["unit"].Value != null)
            //{
            //    buygood.unit = dgvgoods.CurrentRow.Cells["unit"].Value.ToString();
            //}
            //if (dgvgoods.CurrentRow.Cells["typeName1"].Value != null)
            //{
            //    buygood.goodsTypeId = dgvgoods.CurrentRow.Cells["typeName1"].Value.ToString();
            //}
            //if (dgvgoods.CurrentRow.Cells["lowestOff"].Value != null)
            //{
            //    buygood.lowestOff = dgvgoods.CurrentRow.Cells["lowestOff"].Value.ToString();
            //}
            buygood.ShowDialog();
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            this.Left += 2;
            this.Opacity += 0.1;
            if (Opacity == 1)
            {
                this.timer1.Stop();

            }

        }

        private void timer2_Tick(object sender, EventArgs e)
        {
            this.Top = this.Top + 10;
            this.Opacity = this.Opacity - 0.06;
            if (Opacity <= 0)
            {
                this.timer2.Stop();
                this.Close();
            }
        }
        int flag = 0;
        private void ProduceForm_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (flag == 0)
            {
                e.Cancel = true;
                flag = 1;
                this.timer2.Start();
            }
        }

        private void dgvgoods_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            Loaddgv();
            //leixinxianshi();
        }

        private void button2_Click(object sender, EventArgs e)
        {


            //测试，将excel中的sheet1导入到sqlserver中
            string connString = ConfigurationManager.AppSettings["ConnectionString"];
            System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();
            if (fd.ShowDialog() == DialogResult.OK)
            {
                TransferData(fd.FileName, "sheet1", connString);
            }
        }
        public void TransferData(string excelFile, string sheetName, string connectionString)
        {
            ZG_member.BLL.goods adv = new ZG_member.BLL.goods();
            ZG_member.Model.goods advMode = new ZG_member.Model.goods();
            DataTable advTB = adv.GetList("").Tables[0];
            DataSet ds = new DataSet();
            goodsType gt = new goodsType();
            ZG_member.Model.goodsType gtMode = new ZG_member.Model.goodsType();







            //try
            //{

            //    OpenFileDialog openFileDialog = new OpenFileDialog();
            //    openFileDialog.Filter = "Excel文件|*.xls|Excel文件|*.xlsx";
            //    if (openFileDialog.ShowDialog() == DialogResult.OK)
            //    {

            //        FileInfo fileInfo = new FileInfo(openFileDialog.FileName);
            //        string filePath = fileInfo.FullName;
            //        try
            //        {
            //            // string connExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";
            //            string connExcel = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended properties=\"Excel 12.0;Imex=1;HDR=Yes;\"";
            //            OleDbConnection oleDbConnection = new OleDbConnection(connExcel);
            //            oleDbConnection.Open();
            //            DataTable dataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            //            string tableName = dataTable.Rows[0][2].ToString().Trim();
            //            tableName = "[" + tableName.Replace("'", "") + "]";
            //            string query = "SELECT * FROM " + tableName;
            //            DataSet dataSet = new DataSet();
            //            OleDbDataAdapter oleAdapter = new OleDbDataAdapter(query, connExcel);
            //            oleAdapter.Fill(dataSet, tableName);
            //            oleDbConnection.Close();
            //            DataTable dt = dataSet.Tables[0];  //将数据和dataGridView绑   
            //            for (int i = 0; i < dt.Rows.Count; i++)
            //            {
            //                txtContacts.Text += dt.Rows[i][0].ToString() + ",";
            //            }
            //        }
            //        catch
            //        {
            //            string connExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";
            //            // string connExcel = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended properties=\"Excel 12.0;Imex=1;HDR=Yes;\"";
            //            OleDbConnection oleDbConnection = new OleDbConnection(connExcel);
            //            oleDbConnection.Open();
            //            DataTable dataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            //            string tableName = dataTable.Rows[0][2].ToString().Trim();
            //            tableName = "[" + tableName.Replace("'", "") + "]";
            //            string query = "SELECT * FROM " + tableName;
            //            DataSet dataSet = new DataSet();
            //            OleDbDataAdapter oleAdapter = new OleDbDataAdapter(query, connExcel);
            //            oleAdapter.Fill(dataSet, tableName);
            //            oleDbConnection.Close();
            //            DataTable dt = dataSet.Tables[0];  //将数据和dataGridView绑 
            //            int num = dt.Rows.Count;
            //            for (int i = 0; i < dt.Rows.Count; i++)
            //            {
            //                progressBar1.Value = (i + 1) * 100 / num;
            //                lblMsg.Text = ((i + 1) * 100 / num).ToString() + "%";
            //                txtContacts.Text += dt.Rows[i][0].ToString() + ",";
            //                Application.DoEvents();
            //            }
            //        }
            //    }
            //}
            //catch
            //{
            //    MessageBox.Show("表格正处于打开状态或外部表不是所规定的表格类型");
            //}


























            try
            {
                try
                {
                    //获取全部数据   
                    string strConn = "Provider=Microsoft.ACE.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended properties=\"Excel 12.0;Imex=1;HDR=Yes;\"";
                    OleDbConnection conn = new OleDbConnection(strConn);
                    conn.Open();
                    string strExcel = "";
                    OleDbDataAdapter myCommand = null;
                    strExcel = string.Format("select * from [{0}$]", sheetName);
                    myCommand = new OleDbDataAdapter(strExcel, strConn);
                    myCommand.Fill(ds, sheetName);
                    bool bl = true;
                    //如果目标表不存在则创建   
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        int chuxiao = ds.Tables[0].Rows[i]["促销是否"].ToString() == "true" ? 1 : 0;
                        gtMode = gt.GetModelStr(ds.Tables[0].Rows[i]["类型"].ToString());
                        int leixing = gtMode.id;
                        string strSql = "insert into goods values(" + ds.Tables[0].Rows[i]["商品编号"].ToString() + ",'" + ds.Tables[0].Rows[i]["商品名称"].ToString() + "','" + ds.Tables[0].Rows[i]["计量单位"].ToString() + "'," + ds.Tables[0].Rows[i]["商品价格"].ToString() + "," + ds.Tables[0].Rows[i]["促销价格"].ToString() + "," + ds.Tables[0].Rows[i]["最低价格"].ToString() + "," + chuxiao + "," + ds.Tables[0].Rows[i]["库存数量"].ToString() + "," + ds.Tables[0].Rows[i]["促销折扣"].ToString() + "," + leixing + ")";

                        //strSql += string.Format("{0} varchar(255),", c.ColumnName);

                        //if (advTB.Columns[i + 1].ToString() == ds.Tables[0].Columns[i].ToString())
                        //{
                        //for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                        //{
                        //    if (j == ds.Tables[0].Columns.Count - 1)
                        //    {
                        //        string Type = ds.Tables[0].Rows[i][].ToString();
                        //        gtMode=gt.GetModelStr(Type);
                        //        strSql += "'" + gtMode.id + "',";
                        //    }
                        //    else
                        //    {
                        //        strSql += "'" + ds.Tables[0].Rows[i][j].ToString() + "',";
                        //    }
                        //}
                        //}
                        //else
                        //{
                        //    bl = false;
                        //    if (bl == false)
                        //    {
                        //        MessageBox.Show("Excel与数据库标题不一致");
                        //        return;
                        //    }
                        //    break;
                        //}

                        //strSql = strSql.Trim(',') + ")";


                        using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
                        {
                            sqlconn.Open();
                            System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
                            command.CommandText = strSql;
                            command.ExecuteNonQuery();
                            sqlconn.Close();

                        }

                    }
                    // 用bcp导入数据   
                    //using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))
                    //{
                    //    bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
                    //    bcp.BatchSize = 100;//每次传输的行数   
                    //    bcp.NotifyAfter = 100;//进度提示的行数   
                    //    bcp.DestinationTableName = "goods";//目标表   
                    //    bcp.WriteToServer(ds.Tables[0]);
                    //}
                }
                catch (Exception ex)
                {
                    //"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended properties=\"Excel 12.0;Imex=1;HDR=Yes;\"";

                    //获取全部数据   
                    string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFile + ";" + "Extended properties=\"Excel 12.0;Imex=1;HDR=Yes;\"";
                    OleDbConnection conn = new OleDbConnection(strConn);
                    conn.Open();
                    string strExcel = "";
                    OleDbDataAdapter myCommand = null;
                    strExcel = string.Format("select * from [{0}$]", sheetName);
                    myCommand = new OleDbDataAdapter(strExcel, strConn);
                    myCommand.Fill(ds, sheetName);
                    bool bl = true;
                    //如果目标表不存在则创建   
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        int chuxiao = ds.Tables[0].Rows[i]["促销是否"].ToString() == "true" ? 1 : 0;
                        gtMode = gt.GetModelStr(ds.Tables[0].Rows[i]["类型"].ToString());
                        int leixing = gtMode.id;
                        string strSql = "insert into goods values(" + ds.Tables[0].Rows[i]["商品编号"].ToString() + ",'" + ds.Tables[0].Rows[i]["商品名称"].ToString() + "','" + ds.Tables[0].Rows[i]["计量单位"].ToString() + "'," + ds.Tables[0].Rows[i]["商品价格"].ToString() + "," + ds.Tables[0].Rows[i]["促销价格"].ToString() + "," + ds.Tables[0].Rows[i]["最低价格"].ToString() + "," + chuxiao + "," + ds.Tables[0].Rows[i]["库存数量"].ToString() + "," + ds.Tables[0].Rows[i]["促销折扣"].ToString() + "," + leixing + ")";

                        //strSql += string.Format("{0} varchar(255),", c.ColumnName);

                        //if (advTB.Columns[i + 1].ToString() == ds.Tables[0].Columns[i].ToString())
                        //{
                        //for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                        //{
                        //    if (j == ds.Tables[0].Columns.Count - 1)
                        //    {
                        //        string Type = ds.Tables[0].Rows[i][].ToString();
                        //        gtMode=gt.GetModelStr(Type);
                        //        strSql += "'" + gtMode.id + "',";
                        //    }
                        //    else
                        //    {
                        //        strSql += "'" + ds.Tables[0].Rows[i][j].ToString() + "',";
                        //    }
                        //}
                        //}
                        //else
                        //{
                        //    bl = false;
                        //    if (bl == false)
                        //    {
                        //        MessageBox.Show("Excel与数据库标题不一致");
                        //        return;
                        //    }
                        //    break;
                        //}

                        //strSql = strSql.Trim(',') + ")";


                        using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
                        {
                            sqlconn.Open();
                            System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
                            command.CommandText = strSql;
                            command.ExecuteNonQuery();
                            sqlconn.Close();

                        }
                    }
                    
                }
                goods good = new goods();
                DataTable dtt = good.GetList("").Tables[0];
                MessageBox.Show("导入成功");
                dgvgoods.DataSource = dtt;
                Loaddgv();
            }
            catch (Exception ex)
            {
                //System.Windows.Forms.MessageBox.Show(ex.Message);
                MessageBox.Show("请确保Excel格式内容正确!");
                return;
            }
           
        }

        //进度显示   
        void bcp_SqlRowsCopied(object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e)
        {
            this.Text = e.RowsCopied.ToString();
            this.Update();
        }

        private void dgvgoods_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            updateshangping();
        }

        private void button3_Click(object sender, EventArgs e)
        {

            //dgvdaochu.DataSource = dtt;
            //DataTable dt = (DataTable)dgvgoods.DataSource;
            ExportExcel("", dgvgoods);
        }
        public void ExportExcel(string fileName, DataGridView myDGV)
        {
            string saveFileName = "";
            //bool fileSaved = false;
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xls";
            saveDialog.Filter = "Excel文件|*.xls";
            saveDialog.FileName = fileName;
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0) return; //被点了取消 
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Office.Excel组件");
                return;
            }
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 
            int colIndex = 0;

            //写入标题
            for (int i = 0; i < myDGV.ColumnCount; i++)
            {
                if (myDGV.Columns[i].Visible)
                {
                    colIndex++;
                    worksheet.Cells[1, colIndex] = myDGV.Columns[i].HeaderText;
                    //  worksheet.Cells[1, i] = myDGV.Columns[i].HeaderText;
                }
            }
            //写入数值
            for (int r = 0; r < myDGV.Rows.Count; r++)
            {
                colIndex = 0;
                for (int i = 0; i < myDGV.ColumnCount; i++)
                {
                    if (myDGV.Columns[i].Visible)
                    {
                        colIndex++;
                        worksheet.Cells[r + 2, colIndex] = myDGV.Rows[r].Cells[i].Value;
                    }
                }
                System.Windows.Forms.Application.DoEvents();
            }
            worksheet.Columns.EntireColumn.AutoFit();//列宽自适应

            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                    //fileSaved = true;
                }
                catch (Exception ex)
                {
                    //fileSaved = false;
                    MessageBox.Show("导出文件时出错,文件可能正被打开！/n" + ex.Message);
                }

            }
            xlApp.Quit();
            GC.Collect();//强行销毁 
            // if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL

            MessageBox.Show(fileName + "导出成功", "提示", MessageBoxButtons.OK);
        }
    }
}